package org.test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.nokatag.dbgrid.CellParItem;
import org.nokatag.dbgrid.NDBGridData;
import org.nokatag.dbgrid.NDBGridDataItem;
import org.nokatag.dbgrid.NDBGridDataQueryItem;
import org.nokatag.system.BugInfoWrint;

public class MySQLDataServer extends NDBGridData{
	public NDBGridDataItem DataQuery(NDBGridDataQueryItem dq,Connection con) {
		try{
			//----------------------加载查询条件-----------------------------------------------------------
			String sql=dq.getSql();//获取查询SQL语句
			if(dq.getWsql()!=null)//查询条件，已经格化成了?
				sql="select   * from ("+sql+")  temptable_"+System.currentTimeMillis()+" where 1=1 "+ dq.getWsql();
			else
				sql="select   * from ("+sql+")  temptable_"+System.currentTimeMillis()+" where 1=1 ";
			//----------------------计算总记录数--------------------------------------------------------------
			PreparedStatement pa= con.prepareStatement(" select count(1) as conet from  ("+ sql + ")  temptable_" + System.nanoTime(),ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			for(CellParItem c:dq.getParList()){//设置查询条件
				pa.setObject(c.getIndex(),c.getValue(),c.getType());
			}
			ResultSet rsf=pa.executeQuery();
			rsf.next();
			Long lastRow = rsf.getLong("conet");// 一共有多少条记录
			rsf.close();//内部产生的ResultSet一定要关闭
			pa.close();//内部产生的PreparedStatement一定要关闭
			//----------------------获取当前页数据--------------------------------------------------------------
			Long showPage =dq.getShowPage();
			Long pageSize =dq.getPageSize()==null?(lastRow<1?1:lastRow):dq.getPageSize();
			Long pageConut = (lastRow % pageSize == 0) ? (lastRow / pageSize): (lastRow / pageSize + 1);// 总的页数
			if (showPage > pageConut)
				showPage = pageConut;
			if (showPage < 1)
				showPage = 1L;
			Long posion = (showPage - 1) * pageSize;// 游标位置
			String selectSql = "select  * from ( "+sqlpx(dq,sql)+" ) table_"+System.currentTimeMillis()+" limit "+posion+","+pageSize+" ";
			PreparedStatement pa2= con.prepareStatement(selectSql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			for(CellParItem c:dq.getParList()){
				pa2.setObject(c.getIndex(),c.getValue(),c.getType());
			}
			ResultSet rss = pa2.executeQuery();
			ResultSetMetaData rsmd = rss.getMetaData();// 取得列名
			List<Map<String,String>> rows = new ArrayList<Map<String,String>>();
			List<String> cellnames=new ArrayList<String>();//列名 
			while (rss.next()) {//组织数据
				Map<String,String> row=new HashMap<String,String>();
				Map<String, Object> map=new HashMap<String, Object>();//列名=值
				cellnames=new ArrayList<String>();//列名 
				for(int a=1;a<=rsmd.getColumnCount();a++){
					cellnames.add(rsmd.getColumnName(a).toLowerCase());//列名 
					String cellvalue= rss.getString(a);
					map.put(rsmd.getColumnName(a).toLowerCase(), cellvalue);
				}
				for(int a=0;a<cellnames.size();a++){
					String value=formatValue(map.get(cellnames.get(a)),dq.getFormatcell(),cellnames.get(a),dq.getRequest(),map);//格式化数据
					row.put(cellnames.get(a), value);
				}
				rows.add(row);
			}
			rss.close();//内部产生的ResultSet一定要关闭
			pa2.close();//内部产生的PreparedStatement一定要关闭
			//-----组织返回数据 pageConut:总页数,showPage:当前页码,posion+1:当前页起始行,(posion+rows.size()):当前页结束行,Long.valueOf(rows.size()):当前页总记录数,lastRow:共多少条记录,rows:数据,cellnames:列名数据
			NDBGridDataItem nd = new NDBGridDataItem(pageConut, showPage, posion+1, (posion+rows.size()), Long.valueOf(rows.size()),lastRow,rows,cellnames);
			return nd;
		}catch(Exception se){
			se.printStackTrace();
			BugInfoWrint.Print(se);
		}
		return null;
	}
}
